﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Linq;
using UniMana.Utils.Controls;
using UniMana.Data;
using UniMana.Utils.Data;

namespace UniMana.Data
{
    public static class Training_ScoreData
    {
        #region [Tân]
        /// <summary>
        /// Lấy danh sách sinh viên
        /// </summary>
        /// <param name="SubjectClassKey">Mã lớp học phần</param>
        public static IQueryable GetStudenInfo(this Table<Training_Student_Subject_Class> t1, Table<Training_Student> t2, Table<Training_Student_Class> t3  ,int subjectClassKey)
        {
            var studentList = from st in t1
                              join student in t2
                                  on st.Training_StudentKey equals student.Training_StudentKey
                              join student_class in t3
                                    on student.Training_StudentKey equals student_class.Training_StudentKey
                              where st.Training_Subject_ClassKey == subjectClassKey
                              select new
                              {
                                  st.Training_StudentKey,
                                  student_class.ID,
                                  student.LastName,
                                  student.SureName,
                                  student.Birthday,
                                  student.Gender
                              };
            return studentList;
        }
        /// <summary>
        /// Kiểm tra điểm đã được thêm
        /// </summary>
        /// <param name="studentKey">Mã sinh viên</param>
        /// <param name="subjectClassKey">Mã lớp học phần</param>
        /// <param name="scoretypeKey">Mã loại điểm</param>
        /// 
        public static bool CheckScores(this Table<Training_Score> t1, Guid studentKey, int subjectClassKey, int scoreTypeKey)
        {
            var scoreList = from st in t1
                            where st.Training_StudentKey == studentKey
                            where st.Training_Subject_ClassKey == subjectClassKey
                            where st.Training_ScoreTypeKey == scoreTypeKey
                            select st;
            if (scoreList.Count() >0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// Lấy thông tin danh sách sinh viên và điểm
        /// </summary>
        /// <param name="subjectClassKey">Mã lớp học phần</param>
        public static IQueryable GetStudentScoreList(this Table<Training_Student> t1,Table<Training_Student_Class> t2,
                       Table<Training_Student_Subject_Class> t3, Table<Training_Subject_Class> t4,
                        Table<Training_Score> t5, Table<Training_ScoreType> t6,
                        int subjectClassKey)
        {
            var stInfo = from ts in t1
                         join tstc in t2 on ts.Training_StudentKey equals tstc.Training_StudentKey
                         join tssc in t3 on ts.Training_StudentKey equals tssc.Training_StudentKey
                         join tsc in t4 on tssc.Training_Subject_ClassKey equals tsc.Training_Subject_ClassKey
                         join tc in t5 on tssc.Training_StudentKey equals tc.Training_StudentKey
                         join st in t6  on tc.Training_ScoreTypeKey equals st.Training_ScoreTypeKey
                         select new
                         {
                             tstc.Training_StudentKey,
                             MSSV = tstc.ID,
                             HoLot = ts.LastName,
                             Ten = ts.SureName,
                             NgaySinh = ts.Birthday,
                             GioiTinh = ts.Gender,
                             tc.Training_ScoreTypeKey,
                             LoaiDiem = st.Name,
                             Diem = tc.Score
                         };
            return stInfo;
        }
        /// <summary>
        /// Kiểm tra lớp học phần đã có điểm
        /// </summary>
        /// <param name="subjectClassKey">Mã lớp học phần</param>
        public static bool Check(this Table<Training_Score> t1, int subjectClassKey)
        {
            var cl = from classList in t1
                     where classList.Training_Subject_ClassKey == subjectClassKey
                     select classList;
            if (cl.Count() > 0) 
                return true;
            else 
                return false;
        }
        /// <summary>
        /// Đếm số loại điểm
        /// </summary>
        /// <param name="subjectClassKey">Mã lớp học phần</param>
        public static IQueryable CountScoreType(this Table<Training_Score> t1, Table<Training_ScoreType> t2, int subjectClassKey)
        {
            var n = (from st in t1
                     join
                         tst in t2 on st.Training_ScoreTypeKey equals tst.Training_ScoreTypeKey
                     where st.Training_Subject_ClassKey == subjectClassKey
                     select new
                     {
                         st.Training_ScoreTypeKey,
                         tst.Name
                     }).Distinct();
            return n;
        }
        /// <summary>
        /// Lấy thông tin loại điểm
        /// </summary>
        /// <param name="subjectClassKey">Mã lớp học phần</param>
        public static IQueryable getScoreType(this Table<Training_SubjectClass_ScoreType> t1, Table<Training_ScoreType> t2, int subjectClassKey)
        {
            var scoreTypeList = from stl in t1
                                join st in t2 on stl.Training_ScoreTypeKey equals st.Training_ScoreTypeKey
                                where stl.Training_Subject_ClassKey == subjectClassKey
                                select st;
            return scoreTypeList;
        }
        /// <summary>
        /// Lấy loại điểm của một lớp học phần
        /// </summary>
        /// <param name="t1">Table Training_SubjectClass_ScoreType</param>
        /// <param name="t2">Table Training_ScoreType</param>
        /// <param name="subjectClassKey">Mã lớp học  phần</param>
        /// <returns></returns>
        public static List<int> getListScoreTypeClass(this Table<Training_SubjectClass_ScoreType> nSubjectClassScoreType, Table<Training_ScoreType> nScoreType, int subjectClassKey) //Table<Training_Score> t3,
        {
            List<int> scoreTypeList = new List<int>();
            var scoreTypel = from stl in nSubjectClassScoreType
                             join st in nScoreType on stl.Training_ScoreTypeKey equals st.Training_ScoreTypeKey
                             //join sc in t3 on stl.Training_ScoreTypeKey equals sc.Training_ScoreTypeKey
                             where stl.Training_Subject_ClassKey == subjectClassKey
                             orderby st.Training_ScoreTypeKey
                             select st;
            foreach (var s in scoreTypel)
            {
                scoreTypeList.Add(s.Training_ScoreTypeKey);
            }
            return scoreTypeList;
        }
        /// <summary>
        /// Lấy danh sách điểm thuộc một loại điểm của một lớp học phần
        /// </summary>
        /// <param name="t1">Table Training_Score</param>
        /// <param name="trainingSubjectClassKey">Mã lớp học phần</param>
        /// <param name="scoreTypeKey">Mã loại điểm</param>
        /// <returns></returns>
        public static IQueryable getScore(this Table<Training_Score> t1, Table<Training_Student> t2,
                                            Table<Training_Student_Class> t3,Table<Training_Student_Subject_Class> t4,
                                            Table<Training_Subject_Class> t5,
                                            int trainingSubjectClassKey, int scoreTypeKey)
        {
            var scores = from diem in t1 
                         from student in t2
                         from studentclass in t3 
                         from studentsubjectclass in t4
                         from subjectclass in t5 

                         where diem.Training_Subject_ClassKey == studentsubjectclass.Training_Subject_ClassKey
                         where diem.Training_Subject_ClassKey == subjectclass.Training_Subject_ClassKey
                         where diem.Training_StudentKey == studentclass.Training_StudentKey
                         where diem.Training_StudentKey == student.Training_StudentKey
                         where studentsubjectclass.Training_Subject_ClassKey == subjectclass.Training_Subject_ClassKey
                         where studentsubjectclass.Training_TrainingProgramKey == studentclass.Training_TrainingProgramKey
                         where studentsubjectclass.Training_StudentKey == student.Training_StudentKey
                         where student.Training_StudentKey == studentclass.Training_StudentKey

                         where subjectclass.Training_Subject_ClassKey == trainingSubjectClassKey
                         where diem.Training_ScoreTypeKey == scoreTypeKey

                         //where stc.Mainly == true
                         //where studentclass.Mainly ==true
                         orderby studentclass.ID
                         //orderby st.SureName
                         select new
                         {
                             diem.Training_StudentKey,
                             diem.Training_ScoreTypeKey,
                             mark = diem.Score == null ? null : diem.Score,
                             diem.OnlyRead,
                             studentclass.ID,
                             diem.Note
                         };
            return scores;
        }
        public static IQueryable getScores(this Table<Training_Score> tScore, int SubjectClassKey)
        {
            var l = from s in tScore 
                    where s.Training_Subject_ClassKey == SubjectClassKey
                    //where s.Training_ScoreTypeKey == scoreTypeKey
                    select s;
            return l;
        }
        /// <summary>
        /// Lấy điểm tất cả các môn của một sinh viên
        /// </summary>
        /// <param name="tScore">Table Training_Score</param>
        /// <param name="tStudentClass">Table Training_Student_Class</param>
        /// <param name="tSubjectClass">Table Training_Subject_Class</param>
        /// <param name="tSubject">Table Training_Subject</param>
        /// <param name="id">Mã sinh viên</param>
        /// <returns></returns>
        public static IQueryable ViewScoreStudentByTerms(this Table<Training_Score> tScore, Table<Training_Student_Class> tStudentClass,
                                                    Table<Training_Subject_Class> tSubjectClass, Table<Training_Subject> tSubject,
                                                    Table<Catalogue_Term> tTerm, Table<Training_TrainingProgram_Detail> tProgramDetail,
                                                    
                                                    string id, int trainingProgramKey =0)
        {
            var scoretList = from t4 in
                                 (
                                     (from t0 in tScore
                                      join t1 in tStudentClass on t0.Training_StudentKey equals t1.Training_StudentKey
                                      join t2 in tSubjectClass on t0.Training_Subject_ClassKey equals t2.Training_Subject_ClassKey
                                      join t5 in tTerm on t2.Catalogue_TermKey equals t5.Catalogue_TermKey
                                      join t3 in tSubject on new { Training_SubjectKey = Convert.ToInt32(t2.Training_SubjectKey) } equals new { Training_SubjectKey = t3.Training_SubjectKey }
                                      
                                      group new { t1, t3, t0 } by new
                                      {
                                          Term = t5.Name,
                                          subjectClassKey = t2.Training_Subject_ClassKey,
                                          t1.ID,
                                          t3.Training_SubjectKey,
                                          subjectID = t3.ID,
                                          t3.Name,
                                          t0.Score,
                                          t0.Training_ScoreTypeKey,
                                          t3.Accumulated,
                                          t1.Training_TrainingProgramKey,
                                          t1.Mainly,
                                          t1.Training_StudentKey
                                      } into g
                                      select new
                                      {
                                          g.Key.Term,
                                          g.Key.subjectClassKey,
                                          g.Key.ID,
                                          Training_SubjectKey = (System.Int32?)g.Key.Training_SubjectKey,
                                          subjectID = g.Key.subjectID,
                                          Name = g.Key.Name,
                                          Score = (System.Double?)g.Key.Score,
                                          Training_ScoreTypeKey = (System.Int32?)g.Key.Training_ScoreTypeKey,
                                          g.Key.Accumulated,
                                          g.Key.Training_TrainingProgramKey,
                                          g.Key.Mainly,
                                          g.Key.Training_StudentKey
                                      }))
                             where t4.ID == id 
                             where t4.Accumulated == true
                             where t4.Mainly == true
                             //where t4.Training_TrainingProgramKey == infoStudentClass.Properties.Training_TrainingProgramKey
                             orderby
                               t4.Training_SubjectKey,
                               t4.Training_ScoreTypeKey
                             select new
                             {
                                 t4.Term,
                                 t4.subjectClassKey,
                                 t4.ID,
                                 t4.Training_SubjectKey,
                                 t4.subjectID,
                                 t4.Name,
                                 t4.Score,
                                 t4.Training_ScoreTypeKey,
                                 t4.Accumulated,
                                 t4.Training_TrainingProgramKey, 
                                 t4.Mainly,
                                 t4.Training_StudentKey
                             };

            var sc = from studentclass in tStudentClass
                     join programedeltail in tProgramDetail
                     on studentclass.Training_TrainingProgramKey equals programedeltail.Training_TrainingProgramKey
                     where studentclass.ID == id
                     where studentclass.Mainly == true
                     select new
                     {
                         programedeltail.Training_SubjectKey
                     };
            if (trainingProgramKey == 0)
            {
                scoretList = from scorelist in scoretList
                             from sc1 in sc
                             where sc1.Training_SubjectKey == scorelist.Training_SubjectKey
                             select scorelist;
            }
            else
            {
                scoretList = from scorelist in scoretList
                             from sc1 in sc
                             where sc1.Training_SubjectKey == scorelist.Training_SubjectKey
                             where scorelist.Training_TrainingProgramKey == trainingProgramKey
                             select scorelist;
            }
            scoretList = from score in scoretList
                         orderby score.Term
                         orderby score.subjectID
                         orderby score.subjectClassKey
                         select score;
            return scoretList;
        }
        /// <summary>
        /// Lấy điểm của một sinh viên theo học kì
        /// </summary>
        /// <param name="tScore"></param>
        /// <param name="tStudentClass"></param>
        /// <param name="tSubjectClass"></param>
        /// <param name="tSubject"></param>
        /// <param name="tTerm"></param>
        /// <param name="tProgramDetail"></param>
        /// <param name="id">Mã sinh viên</param>
        /// <param name="termKey">Từ học kì</param>
        /// <param name="termKey1">Đến học kì</param>
        /// <returns></returns>
        public static IQueryable ViewScoreStudentByTerm(this Table<Training_Score> tScore, Table<Training_Student_Class> tStudentClass,
                                                    Table<Training_Subject_Class> tSubjectClass, Table<Training_Subject> tSubject,
                                                    Table<Catalogue_Term> tTerm,Table<Training_TrainingProgram_Detail> tProgramDetail,
                                                    string id, int termKey = 0, int termKey1 = 0, int trainingProgramKey =0)
        {
            //Training_Student_ClassInfo infoStudentClass = new Training_Student_ClassInfo(id);
            var scoretList = from t4 in
                                 (
                                     (from t0 in tScore
                                      join t1 in tStudentClass on t0.Training_StudentKey equals t1.Training_StudentKey
                                      join t2 in tSubjectClass on t0.Training_Subject_ClassKey equals t2.Training_Subject_ClassKey
                                      join t5 in tTerm on t2.Catalogue_TermKey equals t5.Catalogue_TermKey
                                      join t3 in tSubject on new { Training_SubjectKey = Convert.ToInt32(t2.Training_SubjectKey) } equals new { Training_SubjectKey = t3.Training_SubjectKey }
                                      group new { t1, t3, t0 } by new
                                      {
                                          Term = t5.Name,
                                          subjectClassKey = t2.Training_Subject_ClassKey,
                                          t1.ID,
                                          t3.Training_SubjectKey,
                                          subjectID = t3.ID,
                                          t3.Name,
                                          t0.Score,
                                          t0.Training_ScoreTypeKey,
                                          t3.Accumulated,
                                          t1.Training_TrainingProgramKey,
                                          t1.Mainly
                                      } into g
                                      select new
                                      {
                                          g.Key.Term,
                                          g.Key.subjectClassKey,
                                          g.Key.ID,
                                          Training_SubjectKey = (System.Int32?)g.Key.Training_SubjectKey,
                                          subjectID = g.Key.subjectID,
                                          Name = g.Key.Name,
                                          Score = (System.Double?)g.Key.Score,
                                          Training_ScoreTypeKey = (System.Int32?)g.Key.Training_ScoreTypeKey,
                                          g.Key.Accumulated,
                                          g.Key.Training_TrainingProgramKey,
                                          g.Key.Mainly
                                      }))
                             where t4.ID == id
                             where (Convert.ToInt32(t4.Term) >= termKey && Convert.ToInt32(t4.Term) <= termKey1)
                             where t4.Accumulated == true 
                             where t4.Mainly == true
                                //t4.Training_TrainingProgramKey == infoStudentClass.Properties.Training_TrainingProgramKey
                             orderby
                               t4.Training_SubjectKey,
                               t4.Training_ScoreTypeKey
                             select new
                             {
                                 t4.Term,
                                 t4.subjectClassKey,
                                 t4.ID,
                                 t4.Training_SubjectKey,
                                 t4.subjectID,
                                 t4.Name,
                                 t4.Score,
                                 t4.Training_ScoreTypeKey,
                                 t4.Accumulated,
                                 t4.Training_TrainingProgramKey,
                                 t4.Mainly
                             };
            var sc = from studentclass in tStudentClass
                     join programedeltail in tProgramDetail
                     on studentclass.Training_TrainingProgramKey equals programedeltail.Training_TrainingProgramKey
                     where studentclass.ID == id
                     select new
                     {
                         programedeltail.Training_SubjectKey
                     };
            if (trainingProgramKey == 0)
            {
                scoretList = from scorelist in scoretList
                             from sc1 in sc
                             where sc1.Training_SubjectKey == scorelist.Training_SubjectKey
                             select scorelist;
            }
            else
            {

                scoretList = from scorelist in scoretList
                             from sc1 in sc
                             where sc1.Training_SubjectKey == scorelist.Training_SubjectKey
                             where scorelist.Training_TrainingProgramKey == trainingProgramKey
                             select scorelist;
            }
            return scoretList;
        }
        /// <summary>
        /// Kiểm tra loại điểm của một lớp học phần
        /// </summary>
        /// <param name="tScore"></param>
        /// <param name="subjectClass">Key lớp học phần</param>
        /// <param name="scoreType">Key loại điểm</param>
        /// <returns></returns>
        public static bool CheckSubjectClassScoreType(this Table<Training_Score> tScore, int subjectClass, int scoreType)
        {
            var count = from c in tScore
                        where c.Training_Subject_ClassKey == subjectClass
                        where c.Training_ScoreTypeKey == scoreType
                        select c;
            if (count.Count() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static IQueryable ViewScoreStudentBySchoolYears(this Table<Training_Score> tScore, Table<Training_Student_Class> tStudentClass,
                                                    Table<Training_Subject_Class> tSubjectClass, Table<Training_Subject> tSubject,
                                                    Table<Catalogue_Term> tTerm,Table<Catalogue_SchoolYear> tSchoolYear, Table<Training_TrainingProgram_Detail> tProgramDetail,
                                                    string id, int trainingProgramKey=0)
        {
            var scoretList = from t4 in
                                 (
                                     (from t0 in tScore
                                      join t1 in tStudentClass on t0.Training_StudentKey equals t1.Training_StudentKey
                                      join t2 in tSubjectClass on t0.Training_Subject_ClassKey equals t2.Training_Subject_ClassKey
                                      join t5 in tTerm on t2.Catalogue_TermKey equals t5.Catalogue_TermKey
                                      join t6 in tSchoolYear on t2.Catalogue_SchoolYearKey equals t6.Catalogue_SchoolYearKey
                                      join t3 in tSubject on new { Training_SubjectKey = Convert.ToInt32(t2.Training_SubjectKey) } equals new { Training_SubjectKey = t3.Training_SubjectKey }
                                      group new { t1, t3, t0 } by new
                                      {
                                          Term = t5.Name,
                                          subjectClassKey = t2.Training_Subject_ClassKey,
                                          t6.Catalogue_SchoolYearKey,
                                          SchoolYear = t6.Name,
                                          t1.ID,
                                          t3.Training_SubjectKey,
                                          subjectID = t3.ID,
                                          t3.Name,
                                          t0.Score,
                                          t0.Training_ScoreTypeKey,
                                          t3.Accumulated,
                                          t1.Training_TrainingProgramKey
                                      } into g
                                      select new
                                      {
                                          g.Key.Term,
                                          g.Key.subjectClassKey,
                                          g.Key.Catalogue_SchoolYearKey,
                                          g.Key.SchoolYear,
                                          g.Key.ID,
                                          Training_SubjectKey = (System.Int32?)g.Key.Training_SubjectKey,
                                          subjectID = g.Key.subjectID,
                                          Name = g.Key.Name,
                                          Score = (System.Double?)g.Key.Score,
                                          Training_ScoreTypeKey = (System.Int32?)g.Key.Training_ScoreTypeKey,
                                          g.Key.Accumulated,
                                          g.Key.Training_TrainingProgramKey
                                      }))
                             where t4.ID == id
                             where t4.Accumulated == true
                             orderby
                               t4.Catalogue_SchoolYearKey,
                               t4.Term,
                               t4.Training_SubjectKey,
                               t4.Training_ScoreTypeKey
                             select new
                             {
                                 t4.Term,
                                 t4.subjectClassKey,
                                 t4.Catalogue_SchoolYearKey,
                                 t4.SchoolYear,
                                 t4.ID,
                                 t4.Training_SubjectKey,
                                 t4.subjectID,
                                 t4.Name,
                                 t4.Score,
                                 t4.Training_ScoreTypeKey,
                                 t4.Accumulated,
                                 t4.Training_TrainingProgramKey
                             };
            var sc = from studentclass in tStudentClass
                     join programedeltail in tProgramDetail
                     on studentclass.Training_TrainingProgramKey equals programedeltail.Training_TrainingProgramKey
                     where studentclass.ID == id
                     select new
                     {
                         programedeltail.Training_SubjectKey
                     };
            scoretList = from scorelist in scoretList
                         from sc1 in sc
                         where sc1.Training_SubjectKey == scorelist.Training_SubjectKey
                         //where scorelist.Catalogue_SchoolYearKey == schoolYearKey
                         select scorelist;
            return scoretList;
        }
        //public static IQueryable ViewScoreStudentBySchoolYears(this Table<Training_Score> tScore, Table<Training_Student_Class> tStudentClass,
        //                                           Table<Training_Subject_Class> tSubjectClass, Table<Training_Subject> tSubject,
        //                                           Table<Catalogue_Term> tTerm, Table<Catalogue_SchoolYear> tSchoolYear, Table<Training_TrainingProgram_Detail> tProgramDetail,
        //                                           string id, int schoolYearKey)
        //{
        //    var scoretList = from t4 in
        //                         (
        //                             (from t0 in tScore
        //                              join t1 in tStudentClass on t0.Training_StudentKey equals t1.Training_StudentKey
        //                              join t2 in tSubjectClass on t0.Training_Subject_ClassKey equals t2.Training_Subject_ClassKey
        //                              join t5 in tTerm on t2.Catalogue_TermKey equals t5.Catalogue_TermKey
        //                              join t6 in tSchoolYear on t2.Catalogue_SchoolYearKey equals t6.Catalogue_SchoolYearKey
        //                              join t3 in tSubject on new { Training_SubjectKey = Convert.ToInt32(t2.Training_SubjectKey) } equals new { Training_SubjectKey = t3.Training_SubjectKey }
        //                              group new { t1, t3, t0} by new
        //                              {
        //                                  Term = t5.Name,
        //                                  subjectClassKey = t2.Training_Subject_ClassKey,
        //                                  t6.Catalogue_SchoolYearKey,
        //                                  SchoolYear = t6.Name,
        //                                  t1.ID,
        //                                  t3.Training_SubjectKey,
        //                                  subjectID = t3.ID,
        //                                  t3.Name,
        //                                  t0.Score,
        //                                  t0.Training_ScoreTypeKey,
        //                                  t3.Accumulated,
        //                                  t1.Training_TrainingProgramKey
        //                              } into g
        //                              select new
        //                              {
        //                                  g.Key.Term,
        //                                  g.Key.subjectClassKey,
        //                                  g.Key.Catalogue_SchoolYearKey,
        //                                  g.Key.SchoolYear,
        //                                  g.Key.ID,
        //                                  Training_SubjectKey = (System.Int32?)g.Key.Training_SubjectKey,
        //                                  subjectID = g.Key.subjectID,
        //                                  Name = g.Key.Name,
        //                                  Score = (System.Double?)g.Key.Score,
        //                                  Training_ScoreTypeKey = (System.Int32?)g.Key.Training_ScoreTypeKey,
        //                                  g.Key.Accumulated,
        //                                  g.Key.Training_TrainingProgramKey
        //                              }))
        //                     where t4.ID == id
        //                     where t4.Accumulated == true
        //                     //where t4.Catalogue_SchoolYearKey == schoolYearKey
        //                     orderby
        //                       t4.Training_SubjectKey,
        //                       t4.Training_ScoreTypeKey
        //                     select new
        //                     {
        //                         t4.Term,
        //                         t4.subjectClassKey,
        //                         t4.Catalogue_SchoolYearKey,
        //                         t4.SchoolYear,
        //                         t4.ID,
        //                         t4.Training_SubjectKey,
        //                         t4.subjectID,
        //                         t4.Name,
        //                         t4.Score,
        //                         t4.Training_ScoreTypeKey,
        //                         t4.Accumulated,
        //                         t4.Training_TrainingProgramKey
        //                     };
        //    var sc = from studentclass in tStudentClass
        //             join programedeltail in tProgramDetail
        //             on studentclass.Training_TrainingProgramKey equals programedeltail.Training_TrainingProgramKey
        //             where studentclass.ID == id
        //             select new
        //             {
        //                 programedeltail.Training_SubjectKey
        //             };
        //    scoretList = from scorelist in scoretList
        //                 from sc1 in sc
        //                 where sc1.Training_SubjectKey == scorelist.Training_SubjectKey
        //                 //where .Catalogue_SchoolYearKey == schoolYearKey
        //                 select scorelist;
        //    return scoretList;
        //}

        public static IQueryable ViewSchooYear(this Table<Training_Score> tScore, Table<Training_Student_Class> tStudentClass,
                                                    Table<Training_Subject_Class> tSubjectClass, Table<Training_Subject> tSubject,
                                                    Table<Catalogue_Term> tTerm, Table<Catalogue_SchoolYear> tSchoolYear, Table<Training_TrainingProgram_Detail> tProgramDetail,
                                                    string id)
        {
            var scoretList = from t4 in
                                 (
                                     (from t0 in tScore
                                      join t1 in tStudentClass on t0.Training_StudentKey equals t1.Training_StudentKey
                                      join t2 in tSubjectClass on t0.Training_Subject_ClassKey equals t2.Training_Subject_ClassKey
                                      join t5 in tTerm on t2.Catalogue_TermKey equals t5.Catalogue_TermKey
                                      join t6 in tSchoolYear on t2.Catalogue_SchoolYearKey equals t6.Catalogue_SchoolYearKey
                                      join t3 in tSubject on new { Training_SubjectKey = Convert.ToInt32(t2.Training_SubjectKey) } equals new { Training_SubjectKey = t3.Training_SubjectKey }
                                      group new { t1, t3, t0 } by new
                                      {
                                          Term = t5.Name,
                                          subjectClassKey = t2.Training_Subject_ClassKey,
                                          t6.Catalogue_SchoolYearKey,
                                          SchoolYear = t6.Name,
                                          t1.ID,
                                          t3.Training_SubjectKey,
                                          subjectID = t3.ID,
                                          t3.Name,
                                          t0.Score,
                                          t0.Training_ScoreTypeKey,
                                          t3.Accumulated,
                                          t1.Training_TrainingProgramKey
                                      } into g
                                      select new
                                      {
                                          g.Key.Term,
                                          g.Key.subjectClassKey,
                                          g.Key.Catalogue_SchoolYearKey,
                                          g.Key.SchoolYear,
                                          g.Key.ID,
                                          Training_SubjectKey = (System.Int32?)g.Key.Training_SubjectKey,
                                          subjectID = g.Key.subjectID,
                                          Name = g.Key.Name,
                                          Score = (System.Double?)g.Key.Score,
                                          Training_ScoreTypeKey = (System.Int32?)g.Key.Training_ScoreTypeKey,
                                          g.Key.Accumulated,
                                          g.Key.Training_TrainingProgramKey
                                      }))
                             where t4.ID == id
                             where t4.Accumulated == true
                             //where t4.Training_TrainingProgramKey == infoStudentClass.Properties.Training_TrainingProgramKey
                             orderby
                               t4.Training_SubjectKey,
                               t4.Training_ScoreTypeKey
                             select new
                             {
                                 t4.Term,
                                 t4.subjectClassKey,
                                 t4.Catalogue_SchoolYearKey,
                                 t4.SchoolYear,
                                 t4.ID,
                                 t4.Training_SubjectKey,
                                 t4.subjectID,
                                 t4.Name,
                                 t4.Score,
                                 t4.Training_ScoreTypeKey,
                                 t4.Accumulated,
                                 t4.Training_TrainingProgramKey
                             };
            var sc = from studentclass in tStudentClass
                     join programedeltail in tProgramDetail
                     on studentclass.Training_TrainingProgramKey equals programedeltail.Training_TrainingProgramKey
                     where studentclass.ID == id
                     select new
                     {
                         programedeltail.Training_SubjectKey
                     };
            var schoolYearList = (from scorelist in scoretList
                         from sc1 in sc
                         where sc1.Training_SubjectKey == scorelist.Training_SubjectKey
                         select new
                         {
                             scorelist.Catalogue_SchoolYearKey,
                             scorelist.SchoolYear
                         }).Distinct();
            return schoolYearList;
        }
        /// <summary>
        /// Lay diem cac mon cua sinh vien trong mot lop hoc phan
        /// </summary>
        /// <param name="tTrainingScore"></param>
        /// <param name="tTrainingSubject"></param>
        /// <param name="tTrainingProgramDetail"></param>
        /// <param name="tTrainingStudentSubjectClass"></param>
        /// <param name="tTrainingSubjectClass"></param>
        /// <param name="tTrainingStudent"></param>
        /// <param name="tTrainingStudentClass"></param>
        /// <param name="tCatalogueTerm"></param>
        /// <param name="tCatalogueSchoolYear"></param>
        /// <param name="subjectClassKey"></param>
        /// <returns></returns>
        public static IQueryable GetSubjectScoreListofStudentBySubjectClass(this Table<Training_Score> tTrainingScore,
                        Table<Training_Subject> tTrainingSubject,
                        Table<Training_TrainingProgram_Detail> tTrainingProgramDetail,
                        Table<Training_Student_Subject_Class> tTrainingStudentSubjectClass,
                        Table<Training_Subject_Class> tTrainingSubjectClass, Table<Training_Student> tTrainingStudent,
                        Table<Training_Student_Class> tTrainingStudentClass,
                        Table<Catalogue_Term> tCatalogueTerm, Table<Catalogue_SchoolYear> tCatalogueSchoolYear,
                        int subjectClassKey)
        {
            var listStudent = from studentSubjectClass in tTrainingStudentSubjectClass
                              where studentSubjectClass.Training_Subject_ClassKey == subjectClassKey
                              select studentSubjectClass.Training_StudentKey;
            var l = (from tScore in tTrainingScore
                    from tSubject in tTrainingSubject
                    from tProgramDetail in tTrainingProgramDetail
                    from tStudentSubjectClass in tTrainingStudentSubjectClass
                    from tSubjectClass in tTrainingSubjectClass
                    from tStudent in tTrainingStudent
                    from tStudentClass in tTrainingStudentClass
                    from tTerm in tCatalogueTerm
                    from tSchoolYear in tCatalogueSchoolYear

                    where tScore.Training_Subject_ClassKey == tSubjectClass.Training_Subject_ClassKey
                    where tSubject.Training_SubjectKey == tProgramDetail.Training_SubjectKey
                    where tSubjectClass.Training_SubjectKey == tSubject.Training_SubjectKey
                    where tSubjectClass.Training_Subject_ClassKey == tStudentSubjectClass.Training_Subject_ClassKey
                    where tSubjectClass.Catalogue_TermKey == tTerm.Catalogue_TermKey
                    where tSubjectClass.Catalogue_SchoolYearKey == tSchoolYear.Catalogue_SchoolYearKey
                    where tStudent.Training_StudentKey == tStudentSubjectClass.Training_StudentKey
                    where tStudentClass.Training_StudentKey == tStudentSubjectClass.Training_StudentKey

                    where tStudentClass.Mainly == true
                    where tSubject.Accumulated == true
                    where tStudentClass.Training_TrainingProgramKey == tStudentSubjectClass.Training_TrainingProgramKey
                    //where tStudentClass.ID == StudentID

                    orderby tSubjectClass.Catalogue_TermKey
                    select new
                    {
                        tStudentClass.Training_StudentKey,
                        tStudentClass.ID,
                        //tStudent.LastName,
                        //tStudent.SureName,
                        //tStudent.Gender,
                        //Birthday=tStudent.Birthday == null ? tStudent.ExceptionBirthday:tStudent.Birthday.ToString(),
                        //tStudent.PlaceOfBirth,
                        AverageScore = tStudentSubjectClass.AverageScore == null ? 0 : tStudentSubjectClass.AverageScore,
                        AverageScoreFinal = tStudentSubjectClass.AverageScoreFinal == null ? 0 : tStudentSubjectClass.AverageScoreFinal,
                        SubjectID = tSubject.ID,
                        TotalCredits = tSubject.TotalCredits == null ? 0 : tSubject.TotalCredits,
                        //tSubjectClass.Training_Subject_ClassKey,
                        SubjectClassID=tSubjectClass.ID,
                        tSubjectClass.Catalogue_TermKey,
                        tSubjectClass.Catalogue_SchoolYearKey
                    }).Distinct();
            l = from l1 in l
                where listStudent.Contains(l1.Training_StudentKey)
                select l1;
            return l.OrderBy(p=>p.Training_StudentKey);
        }
        /// <summary>
        /// Lay danh sach diem cac mon sinh vien da hoc theo ma sinh vien
        /// </summary>
        /// <param name="tTrainingScore"></param>
        /// <param name="tTrainingSubject"></param>
        /// <param name="tTrainingProgramDetail"></param>
        /// <param name="tTrainingStudentSubjectClass"></param>
        /// <param name="tTrainingSubjectClass"></param>
        /// <param name="tTrainingStudent"></param>
        /// <param name="tTrainingStudentClass"></param>
        /// <param name="tCatalogueTerm"></param>
        /// <param name="tCatalogueSchoolYear"></param>
        /// <param name="studentID"></param>
        /// <param name="termFrom"></param>
        /// <param name="termTo"></param>
        /// <returns></returns>
        public static IQueryable GetSubjectScoreListofStudentByID(this Table<Training_Score> tTrainingScore,
                       Table<Training_Subject> tTrainingSubject,
                       Table<Training_TrainingProgram_Detail> tTrainingProgramDetail,
                       Table<Training_Student_Subject_Class> tTrainingStudentSubjectClass,
                       Table<Training_Subject_Class> tTrainingSubjectClass, Table<Training_Student> tTrainingStudent,
                       Table<Training_Student_Class> tTrainingStudentClass,
                       Table<Catalogue_Term> tCatalogueTerm, Table<Catalogue_SchoolYear> tCatalogueSchoolYear,
                       string studentID, int fromTerm = -1, int toTerm = -1, int trainingProgramKey =-1)
        {
            var l = (from tScore in tTrainingScore
                     from tSubject in tTrainingSubject
                     from tProgramDetail in tTrainingProgramDetail
                     from tStudentSubjectClass in tTrainingStudentSubjectClass
                     from tSubjectClass in tTrainingSubjectClass
                     from tStudent in tTrainingStudent
                     from tStudentClass in tTrainingStudentClass
                     from tTerm in tCatalogueTerm
                     from tSchoolYear in tCatalogueSchoolYear

                     where tScore.Training_Subject_ClassKey == tSubjectClass.Training_Subject_ClassKey
                     where tSubject.Training_SubjectKey == tProgramDetail.Training_SubjectKey
                     where tSubjectClass.Training_SubjectKey == tSubject.Training_SubjectKey
                     where tSubjectClass.Training_Subject_ClassKey == tStudentSubjectClass.Training_Subject_ClassKey
                     where tSubjectClass.Catalogue_TermKey == tTerm.Catalogue_TermKey
                     where tSubjectClass.Catalogue_SchoolYearKey == tSchoolYear.Catalogue_SchoolYearKey
                     where tStudent.Training_StudentKey == tStudentSubjectClass.Training_StudentKey
                     where tStudentClass.Training_StudentKey == tStudentSubjectClass.Training_StudentKey

                     where tStudentClass.Mainly == true
                     where tSubject.Accumulated == true
                     where tStudentClass.Training_TrainingProgramKey == tStudentSubjectClass.Training_TrainingProgramKey
                     where tStudentClass.ID == studentID

                     orderby tSubjectClass.Catalogue_TermKey
                     select new
                     {
                         tStudentClass.Training_StudentKey,
                         tStudentClass.ID,
                         tStudent.LastName,
                         tStudent.SureName,
                         tStudent.Gender,
                         Birthday = tStudent.Birthday == null ? tStudent.ExceptionBirthday : tStudent.Birthday.ToString(),
                         tStudent.PlaceOfBirth,
                         AverageScore = tStudentSubjectClass.AverageScore == null ? 0 : tStudentSubjectClass.AverageScore,
                         AverageScoreFinal = tStudentSubjectClass.AverageScoreFinal == null ? 0 : tStudentSubjectClass.AverageScoreFinal,
                         SubjectID = tSubject.ID,
                         TotalCredits = tSubject.TotalCredits == null ? 0 : tSubject.TotalCredits,
                         tSubjectClass.Training_Subject_ClassKey,
                         SubjectClassID = tSubjectClass.ID,
                         tSubjectClass.Catalogue_TermKey,
                         tSubjectClass.Catalogue_SchoolYearKey,
                         TermName = tTerm.Name,
                         SchoolYearName = tSchoolYear.Name,
                         SubjectName = tSubject.Name,
                         tStudentClass.Training_TrainingProgramKey
                     }).Distinct();
            if (fromTerm != -1 && toTerm != -1)
            {
               l = l.Where(t => t.Catalogue_TermKey >= fromTerm && t.Catalogue_TermKey <= toTerm);
            }
            if (trainingProgramKey != -1)
            {
                l = l.Where(p => p.Training_TrainingProgramKey == trainingProgramKey);
            }
            return l.OrderBy(p=>p.Catalogue_TermKey);
        }
#endregion 

        #region [Chi]
        private static IQueryable GetStudentScoreBySchoolYear_Major(
                    Table<Training_Subject> subject,
                    Table<Training_Subject_Class> subject_Class,
                    Table<Training_Student_Subject_Class> student_Subject_Class,
                    Table<Training_TrainingProgram> trainingProgram,
                    Table<Training_Student> student,
                    Table<Training_Score_Transfer> score_Transfer,
                    int schoolYearKey = 0,
                    int majorKey = 0)
        {
            var Query = from sj in subject
                        join sjc in subject_Class on sj.Training_SubjectKey equals sjc.Training_SubjectKey
                        join st_sjc in student_Subject_Class on sjc.Training_Subject_ClassKey equals st_sjc.Training_Subject_ClassKey
                        join st in student on st_sjc.Training_StudentKey equals st.Training_StudentKey
                        join trp in trainingProgram on st_sjc.Training_TrainingProgramKey equals trp.Training_TrainingProgramKey
                        where
                          sjc.Catalogue_SchoolYearKey == schoolYearKey
                        orderby
                          trp.Catalogue_MajorKey,
                          trp.Catalogue_CourseKey,
                          trp.Catalogue_LevelKey,
                          st_sjc.Training_StudentKey,
                          st.LastName,
                          st.SureName,
                          st.Gender,
                          st.Birthday,
                          st.Email,
                          st.HomePhone,
                          st.CellPhone,
                          st.WorkPhone,
                          sj.Training_SubjectKey,
                          sjc.Training_Subject_ClassKey
                        select new
                        {
                            trp.Catalogue_MajorKey,
                            st_sjc.Training_StudentKey,
                            trp.Catalogue_CourseKey,
                            trp.Catalogue_LevelKey,
                            sj.Training_SubjectKey,
                            sjc.Training_Subject_ClassKey,
                            st_sjc.AverageScore,
                            st_sjc.AverageScoreFinal,
                            sj.TotalCredits,
                            st.LastName,
                            st.SureName,
                            Gender = st.Gender == true ? "Nữ" : "Nam",
                            st.Birthday,
                            st.Email,
                            st.HomePhone,
                            st.CellPhone,
                            st.WorkPhone
                        };
            var Query_ = from st_sjc in student_Subject_Class
                         join sc_tr in score_Transfer on st_sjc.Training_Subject_ClassKey equals sc_tr.Training_Subject_ClassKey
                         join sjc in subject_Class on st_sjc.Training_Subject_ClassKey equals sjc.Training_Subject_ClassKey
                         join sj in subject on sjc.Training_SubjectKey equals sj.Training_SubjectKey
                         join st in student on st_sjc.Training_StudentKey equals st.Training_StudentKey
                         join trp in trainingProgram on sc_tr.Training_TrainingProgramKeyNew equals trp.Training_TrainingProgramKey
                         where
                          st_sjc.Training_StudentKey == sc_tr.Training_StudentKey &&
                          sc_tr.Training_TrainingProgramKey == st_sjc.Training_TrainingProgramKey &&
                           sjc.Catalogue_SchoolYearKey == 1
                         orderby
                           sc_tr.Training_TrainingProgramKeyNew,
                           trp.Catalogue_MajorKey,
                           trp.Catalogue_CourseKey,
                           trp.Catalogue_LevelKey,
                           st.Training_StudentKey,
                           st.LastName,
                           st.SureName,
                           st.Gender,
                           st.Birthday,
                           st.Email,
                           st.HomePhone,
                           st.CellPhone,
                           sjc.Training_Subject_ClassKey,
                           sj.Training_SubjectKey
                         select new
                         {
                             trp.Catalogue_MajorKey,
                             st.Training_StudentKey,
                             trp.Catalogue_CourseKey,
                             trp.Catalogue_LevelKey,
                             sj.Training_SubjectKey,
                             sjc.Training_Subject_ClassKey,
                             st_sjc.AverageScore,
                             st_sjc.AverageScoreFinal,
                             sj.TotalCredits,
                             st.LastName,
                             st.SureName,
                             Gender = st.Gender == true ? "Nữ" : "Nam",
                             st.Birthday,
                             st.Email,
                             st.HomePhone,
                             st.CellPhone,
                             st.WorkPhone
                         };
            if (majorKey > 0)
            {
                Query = Query.Where(q => q.Catalogue_MajorKey == majorKey);
                Query_ = Query_.Where(q => q.Catalogue_MajorKey == majorKey);
            }
            var Q = Query.Union(Query_);
            return Query.Distinct();
        }
        public static DataTable Scoring(
                    Table<Training_Subject> subject,
                    Table<Training_Subject_Class> subject_Class,
                    Table<Training_Student_Subject_Class> student_Subject_Class,
                    Table<Training_TrainingProgram> trainingProgram,
                    Table<Training_Student> student,
                    Table<Training_Score_Transfer> score_Transfer,
                    int schoolYearKey = 0,
                    int majorKey = 0)
        {
            var Student_Score = GetStudentScoreBySchoolYear_Major(
                    subject,
                    subject_Class,
                    student_Subject_Class,
                    trainingProgram,
                    student,
                    score_Transfer,
                    schoolYearKey,
                    majorKey);
            DataTable dt = new DataTable();
            dt = Student_Score.ToDataTable();
            DataTable Data = new DataTable();
            if (dt.Rows.Count > 0)
            {
                Data.Columns.Add("Catalogue_MajorKey");
                Data.Columns.Add("Training_StudentKey");
                Data.Columns.Add("Catalogue_CourseKey");
                Data.Columns.Add("Catalogue_LevelKey");
                Data.Columns.Add("AVG_Score");
                Data.Columns.Add("LastName");
                Data.Columns.Add("SureName");
                Data.Columns.Add("Gender");
                Data.Columns.Add("Birthday");
                Data.Columns.Add("Email");
                Data.Columns.Add("HomePhone");
                Data.Columns.Add("CellPhone");
                Data.Columns.Add("WorkPhone");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    int TotalCredit = 0;
                    int TotalScore = 0;
                    for (int j = i; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[i]["Catalogue_MajorKey"].ToString() == dt.Rows[j]["Catalogue_MajorKey"].ToString() &&
                            dt.Rows[i]["Training_StudentKey"].ToString() == dt.Rows[j]["Training_StudentKey"].ToString())
                        {
                            if (dt.Rows[j]["AverageScoreFinal"] != DBNull.Value)
                            {
                                TotalScore += Convert.ToInt16(dt.Rows[j]["AverageScoreFinal"]) * Convert.ToInt16(dt.Rows[j]["TotalCredits"]);
                            }
                            else
                            {
                                if (dt.Rows[j]["AverageScore"] != DBNull.Value)
                                {
                                    TotalScore += Convert.ToInt16(dt.Rows[j]["AverageScore"]) * Convert.ToInt16(dt.Rows[j]["TotalCredits"]);
                                }
                                else
                                {
                                }
                            }
                            if (dt.Rows[j]["TotalCredits"] != DBNull.Value)
                            {
                                TotalCredit += Convert.ToInt16(dt.Rows[j]["TotalCredits"]);
                            }
                        }
                        else
                        {
                            i = j;
                            break;
                        }
                    }
                    Data.Rows.Add();
                    Data.Rows[Data.Rows.Count - 1]["Catalogue_MajorKey"] = dt.Rows[i]["Catalogue_MajorKey"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["Training_StudentKey"] = dt.Rows[i]["Training_StudentKey"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["Catalogue_CourseKey"] = dt.Rows[i]["Catalogue_CourseKey"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["Catalogue_LevelKey"] = dt.Rows[i]["Catalogue_LevelKey"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["AVG_Score"] = Math.Round(TotalScore / (double)TotalCredit, 4);
                    Data.Rows[Data.Rows.Count - 1]["LastName"] = dt.Rows[i]["LastName"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["SureName"] = dt.Rows[i]["SureName"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["Gender"] = dt.Rows[i]["Gender"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["Birthday"] = dt.Rows[i]["Birthday"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["Email"] = dt.Rows[i]["Email"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["HomePhone"] = dt.Rows[i]["HomePhone"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["CellPhone"] = dt.Rows[i]["CellPhone"].ToString();
                    Data.Rows[Data.Rows.Count - 1]["WorkPhone"] = dt.Rows[i]["WorkPhone"].ToString();
                }
            }
            else
            {
                Data.Columns.Add();
                Data.Rows.Add();
                Data.Rows[0][0] = "@000@";
            }
            return Data;
        }
        #endregion

        #region
        /// <summary>
        /// Lấy danh sách các loại điểm, phần trăm và điểm của sinh viên học trong lớp học phần
        /// </summary>
        /// <param name="nTraining_Score"></param>
        /// <param name="m_strTraining_StudentKey"></param>
        /// <param name="m_iTraining_Subject_ClassKey"></param>
        /// <returns></returns>

        public static IQueryable GetListScoreType(this Table<Training_Score> nTraining_Score,
            Table<Training_SubjectClass_ScoreType> nTraining_SubjectClass_ScoreType,
            string m_strTraining_StudentKey,
            int m_iTraining_Subject_ClassKey)
        {
            var values = from t in nTraining_Score
                         join t0 in nTraining_SubjectClass_ScoreType
                               on new { t.Training_Subject_ClassKey, t.Training_ScoreTypeKey }
                           equals new { t0.Training_Subject_ClassKey, t0.Training_ScoreTypeKey }
                         where
                           t.Training_Subject_ClassKey == m_iTraining_Subject_ClassKey &&
                           t.Training_StudentKey.ToString() == m_strTraining_StudentKey
                         select new
                         {
                             t.Training_StudentKey,
                             t.Training_Subject_ClassKey,
                             t.Training_ScoreTypeKey,
                             t.Score,
                             t0.DefaultPercent
                         };

            return values;
        }
        #endregion
    }
}
